# This file is part of the Lumenn project 
# at http://code.google.com/p/lumenn/
#
# Released under the GPLv3 at http://www.gnu.org/copyleft/gpl.html
# Designed for pygtk and the Maemo platform.
#
# Copyright 2008 by Jackson Yee (jackson@gotpossum.com)

import sqlite3 as sqlite
import decimal
import datetime
import re
import types

import lumenn.log as log

UNIQUE	=	0x01
INDEX		=	0x02

# *********************************************************************
class DBType(object):
	
	# -------------------------------------------------------------------
	def __init__(self, 
			name, 
			flags 		= 0, 
			default 	= None, 
			validator = None,
			fieldname	=	None):
			
		self.name				=	name
		self.flags			=	flags
		self.default		=	default
		self.validator	=	validator
		
		if not fieldname:
			self.fieldname	=	name
		else:
			self.fieldname	=	fieldname

# *********************************************************************
class PrimaryKey(DBType):
	
	# -------------------------------------------------------------------
	def __init__(self, 
			name, 
			flags = 0, 
			default = None, 
			validator = None,
			fieldname	=	None):
		DBType.__init__(self,
			name, 
			flags | UNIQUE,
			default, 
			validator,
			fieldname,)

# *********************************************************************
class ForeignKey(DBType):
	
	# -------------------------------------------------------------------
	def __init__(self, 
			name, 
			app,
			table,
			idname):
		DBType.__init__(self, name)
		self.app		=	app
		self.table	=	table
		self.idname	=	idname

# *********************************************************************
class Key(DBType):
	
	# -------------------------------------------------------------------
	def __init__(self, 
			name, 
			flags = 0, 
			default = None, 
			validator = None,
			fieldname	=	None):
		DBType.__init__(self, name, flags, default, validator, fieldname)
		
# *********************************************************************
class Int(DBType):
	
	# -------------------------------------------------------------------
	def __init__(self, 
			name, 
			flags = 0, 
			default = None, 
			validator = None,
			fieldname	=	None):
		DBType.__init__(self, name, flags, default, validator, fieldname)

# *********************************************************************
class Long(DBType):
	
	# -------------------------------------------------------------------
	def __init__(self, 
			name, 
			flags = 0, 
			default = None, 
			validator = None,
			fieldname	=	None):
		DBType.__init__(self, name, flags, default, validator, fieldname)
		
# *********************************************************************
class Float(DBType):
	
	# -------------------------------------------------------------------
	def __init__(self, 
			name, 
			flags = 0, 
			default = None, 
			validator = None,
			fieldname	=	None):
		DBType.__init__(self, name, flags, default, validator, fieldname)

# *********************************************************************
class Text(DBType):
	
	# -------------------------------------------------------------------
	def __init__(self, 
			name, 
			flags = 0, 
			default = None, 
			validator = None,
			fieldname	=	None):
		DBType.__init__(self, name, flags, default, validator, fieldname)

# *********************************************************************
class Bool(DBType):
	
	# -------------------------------------------------------------------
	def __init__(self, 
			name, 
			flags = 0, 
			default = None, 
			validator = None,
			fieldname	=	None):
		DBType.__init__(self, name, flags, default, validator, fieldname)
		
# *********************************************************************
class Decimal(DBType):
	
	# -------------------------------------------------------------------
	def __init__(self, 
			name, 
			flags = 0, 
			default = None, 
			validator = None,
			fieldname	=	None):
		DBType.__init__(self, name, flags, default, validator, fieldname)

# *********************************************************************
class Byte(DBType):
	
	# -------------------------------------------------------------------
	def __init__(self, 
			name, 
			flags = 0, 
			default = None, 
			validator = None,
			fieldname	=	None):
		DBType.__init__(self, name, flags, default, validator, fieldname)
		
# *********************************************************************
class Pickle(DBType):
	
	# -------------------------------------------------------------------
	def __init__(self, 
			name, 
			flags = 0, 
			default = None, 
			validator = None,
			fieldname	=	None):
		DBType.__init__(self, name, flags, default, validator, fieldname)
		
# *********************************************************************
class DateTime(DBType):
	
	# -------------------------------------------------------------------
	def __init__(self, 
			name, 
			flags = 0, 
			default = None, 
			validator = None,
			fieldname	=	None):
		DBType.__init__(self, name, flags, default, validator, fieldname)

# *********************************************************************
class List(DBType):
	
	# -------------------------------------------------------------------
	def __init__(self, 
			name, 
			flags = 0, 
			default = None, 
			validator = None,
			fieldname	=	None):
		DBType.__init__(self, name, flags, default, validator, fieldname)

# *********************************************************************
class BackEnd(object):
	
	# -------------------------------------------------------------------
	def __init__(self, 
			database, 
			host			=	None, 
			username	=	None, 
			password	=	None,
			):	
		self.tables	=	{}
		self.Open(database, host, username, password)

# *********************************************************************
class SQLBackEnd(BackEnd):
	# -------------------------------------------------------------------
	def __init__(self, 
			database, 
			host			=	None, 
			username	=	None, 
			password	=	None,
			):	
		BackEnd.__init__(self, database, host, username, password)
	
	# -------------------------------------------------------------------
	def Save(self, table, fields):
		
		c	=	self.conn.cursor()
		
		if type(fields) is types.ListType:
			for f in fields:
				self._SaveHelper(table, f, c)
		else:
			self._SaveHelper(table, fields, c)
		
		self.conn.commit()
		
	# -------------------------------------------------------------------
	def _SaveHelper(self, table, fields, c):
		if fields['id']:
			id		=	fields['id']
			del	fields['id']
			keys	=	[]
			for k in fields.keys():
				keys.append(' %s=?' % k)
			
			q	=	'UPDATE %s SET %s WHERE id = %s' % (
				table, 
				','.join(keys), 
				id,
			)
		else:
			q	=	'INSERT INTO %s(%s) VALUES(%s)' % (
				table, 
				','.join( fields.keys() ), 
				','.join( '?' * len(fields) ), 
			)			
		
		log.Log(log.TRACE, 'Debug: SQLBackend.Save: %s - %s' % (q, fields.values()) )
		
		c.execute(q, fields.values())
		
	# -------------------------------------------------------------------
	def Create(self, table, fields):
		cols	=	[]
		
		for f in fields:
			cols.append( '%s %s' % (f.fieldname, self.GetType(f) ) )
		
		q	=	'CREATE TABLE %s(%s)' % (table, ','.join(cols) )
		
		log.Log(log.TRACE, 'Debug: SQLBackend.Create: %s' % q)
		
		try:
			c	=	self.conn.cursor()
			self.conn.execute(q)
			self.conn.commit()
		except Exception, e:
			if 'exists' in str(e):
				pass
			else:
				log.Log(log.ERROR, 'Problem creating table %s: %s' % (table, e) )
	
	# -------------------------------------------------------------------
	def GetType(self, f):
		t	=	None
		
		if isinstance(f, Int) or isinstance(f, Long):
			t	= 'INTEGER'
		elif isinstance(f, Float):
			t	=	'DOUBLE'
		elif isinstance(f, Text):
			t	=	'TEXT'
		elif isinstance(f, DateTime):
			t	=	'DATETIME'
		elif isinstance(f, Decimal):
			t	=	'DECIMAL'
		elif isinstance(f, Bool):
			t	=	'BYTE'
		elif isinstance(f, PrimaryKey):
			t	=	'INTEGER PRIMARY KEY'
		else:
			t	=	'BLOB'
			
		flags	=	''
		
		if f.flags & UNIQUE:
			flags	=	flags + 'UNIQUE '
			
		if f.flags & INDEX:
			flags	=	flags + 'INDEX '
		
		return t
		
	# -------------------------------------------------------------------
	def Find(self, fields, table, cond):
		
		if fields:
			q	=	'SELECT %s FROM %s %s' % (','.join(fields), table, cond)
		else:
			q	=	'SELECT %s FROM %s %s' % ('*', table, cond)
		
		log.Log(log.TRACE, 'Debug: SQLBackend.Find: %s' % q)
			
		c	=	self.conn.cursor()
		c.execute(q)
		
		rows	=	[]
		fields	=	[d[0] for d in c.description]
		
		for r in c:
			d	=	{}
			
			for i in range(0, len(fields) ):
				d[ fields[i] ]	=	r[i]
				
			rows.append(d)
		
		return rows
	
	# -------------------------------------------------------------------
	def Delete(self, table, cond):
		q	=	'DELETE FROM %s %s' % (table, cond)
		
		log.Log(log.TRACE, 'Debug: SQLBackend.Delete: %s' % q)
			
		c	=	self.conn.cursor()
		c.execute(q)
		self.conn.commit()
		
	# -------------------------------------------------------------------
	def DeleteObj(self, table, id):
		q	=	'DELETE FROM %s WHERE id = ?' % table
		
		log.Log(log.TRACE, "Debug: SQLBackend.DeleteObj: '%s' with id %s" % (q, id) )
			
		c	=	self.conn.cursor()
		c.execute(q, (id, ) )
		self.conn.commit()
		
	# -------------------------------------------------------------------
	def Count(self, table, cond = None):
		
		if cond:
			q	=	'SELECT COUNT(*) FROM %s %s' % (table, cond)
		else:
			q	=	'SELECT COUNT(*) FROM %s' % table
			
		log.Log(log.TRACE, 'Debug: SQLBackend.Count: %s' % q)
		
		try:
			c	=	self.conn.cursor()
			c.execute(q)
			r	=	c.fetchone()
			return r[0]
		except:
			return 0
		
	# -------------------------------------------------------------------
	def Drop(self, table):
		c	=	self.conn.cursor()
		c.execute('DROP TABLE %s' % table)
		self.conn.commit()
		
	# -------------------------------------------------------------------
	def Begin(self):
		c	=	self.conn.cursor()
		c.execute('BEGIN TRANSACTION')
	
	# -------------------------------------------------------------------
	def Commit(self):
		self.conn.commit()
		
	# -------------------------------------------------------------------
	def Rollback(self):
		self.conn.rollback()

# *********************************************************************
class SQLite(SQLBackEnd):
	
	# -------------------------------------------------------------------
	def __init__(self, 
			database, 
			host			=	None, 
			username	=	None, 
			password	=	None,
			):	
		SQLBackEnd.__init__(self, database, host, username, password)
		
	# -------------------------------------------------------------------
	def Open(self, 
			database, 
			host			=	None, 
			username	=	None, 
			password	=	None,
			):	
		self.conn	=	sqlite.connect(database)		
		

# *********************************************************************
class DB(object):
	"""Represents a particular group of objects such as an SQLite database,
	PostgreSQL schema, web server, or anything else that you care to add.
	
	A DB can be thought of as the interface to a group of objects for
	search, storage, and retrival. Although a database backend is the 
	most common example, flat files, SOAP/JSON servers, or anything else
	can be used as a store backend as well.
	
	The provided store is designed for use with the default SQLite 
	database provided with Python as of version 2.5. Contributions are
	welcome for other backends.
	"""
	# -------------------------------------------------------------------
	def __init__(self, 
			database, 
			dbtype		=	None, 
			host			=	None, 
			username	=	None, 
			password	=	None,
		):
		"""Initializes the connection and system tables using the 
		database connection given.
		"""
		if dbtype == None:
			self.backend			=	SQLite(database)
		else:
			raise NotImplementedError('Database type %s is not supported' % dbtype)
		
		self.database	=	database
		self.dbtype		=	dbtype
		self.host			=	host
		self.username	=	username
		
		self.schemas	=	[]
		self.tables		=	[]
	
	# -------------------------------------------------------------------
	def GetTable(self, obj):
		try:
			return obj._table
		except:
			name	=	obj.__class__.__name__
			
			if name == 'type':
				m	=	re.match(".*\.(.*)\'.*", str(obj))
				return m.group(1).lower()
				
			return name.lower()
		
	# -------------------------------------------------------------------
	def Create(self, obj):
		table	=	self.GetTable(obj)
			
		try:
			fields	=	obj._fields
		except:
			fields	=	[]
			
			for k, v in self.GetFields(obj).iteritems():
				if k == 'id':
					fields.append( PrimaryKey(k) )
					continue
				
				if isinstance(v, int):
					fields.append( Int(k) )
				elif isinstance(v, long):
					fields.append( Long(k) )
				elif isinstance(v, float):
					fields.append( Float(k) )
				elif isinstance(v, datetime.datetime):
					fields.append( DateTime(k) )
				elif isinstance(v, str):
					fields.append( Text(k) )
				elif isinstance(v, decimal.Decimal):
					fields.append( Decimal(k) )
				elif isinstance(v, bool):
					fields.append( Bool(k) )
				else:
					fields.append( Pickle(k) )
		
		self.backend.Create(table, fields)			
		
	# -------------------------------------------------------------------
	def Save(self, obj):
		"""Saves the object into the current backend.
		
		The data types of the individual properties will be automatically
		deduced using isinstance(). 
		"""
		if type(obj) is types.ListType:
			if not len(obj):
				return
			
			table	=	self.GetTable(obj[0])
			fields	=	[ self.GetFields(o) for o in obj ]
		else:		
			table	=	self.GetTable(obj)
			fields	=	self.GetFields(obj)
			
		try:
			self.backend.Save(table, fields)
		except:
			if type(obj) is types.ListType:
				self.Create(obj[0])
			else:
				self.Create(obj)
			self.backend.Save(table, fields)			
			
	# -------------------------------------------------------------------
	def GetFields(self, obj):
		fields	=	{}
		
		if hasattr(obj, '_fields'):
			for f in obj._fields:
				fields[f]	=	getattr(obj, f.fieldname)
		else:
			for f in dir(obj):
				if f[0] != '_':
					o	=	getattr(obj, f)
					t	=	type(o)
					
					if t is types.FunctionType or t is types.MethodType:
						continue
					
					fields[f]	=	o
		
		if 'id' not in fields:
			fields['id']	=	None
		
		return fields
		
	# -------------------------------------------------------------------
	def Begin(self):
		self.backend.Begin()
		
	# -------------------------------------------------------------------
	def Commit(self):
		self.backend.Commit()
		
	# -------------------------------------------------------------------
	def Rollback(self):
		self.backend.Rollback()		
		
	# -------------------------------------------------------------------
	def Find(self, obj, cond, fields = None):
		table	=	self.GetTable(obj)
		
		if fields:	
			cols	=	fields
		else:
			cols	=	None
		
		objs	=	[]
		
		for d in self.backend.Find(cols, table, cond):
			o	=	obj()
				
			for k, v in d.iteritems():
				setattr(o, k, v)
				
			objs.append(o)
		
		return objs
		
	# -------------------------------------------------------------------
	def Get(self, obj, id):
		return self.Find(obj, 'WHERE id = %s' % id)[0]
		
	# -------------------------------------------------------------------
	def Delete(self, obj, cond = None):
		table	=	self.GetTable(obj)
		
		if cond:
			try:				
				self.backend.Delete(table, cond)
			except Exception, e:
				log.Log(log.ERROR, 'Unable to delete %s with condition %s: %s' % 
					(table, cond, e)
				)
		else:				
			try:				
				self.backend.DeleteObj(table, obj.id)
			except Exception, e:
				log.Log(log.ERROR, 'Unable to delete %s object with id %s: %s' % 
					(table, obj.id, e)
				)
			
	# -------------------------------------------------------------------
	def Drop(self, obj):
		table	=	self.GetTable(obj)
		self.backend.Drop(table)
			
	# -------------------------------------------------------------------
	def Count(self, obj, cond = None):
		table	=	self.GetTable(obj)
		return self.backend.Count(table, cond)
		
# *********************************************************************
class ListAdapter(object):
	
	# -------------------------------------------------------------------
	def __init__(self, db, objclass):
		self.db					=	db
		self.objclass		=	objclass
		self.sort				=	[]
		self.count			=	-1
		self.cache			=	[]
		self.cachelimit	=	32
		self.cachestart	=	0
		self.filter			=	''
		
	# -------------------------------------------------------------------
	def append(self, obj):
		self.db.Save(obj)
		self.count	=	-1
		
	# -------------------------------------------------------------------
	def SetFilter(self, cond):
		self.filter	=	cond
		self.count	=	-1
		self.cache	=	[]
		
	# -------------------------------------------------------------------
	def IsEmpty(self):
		s	=	self.db.Find(
			self.objclass, 
			'%s LIMIT 1' % self.filter,
		)
		if s:
			return False
		
		return True
		
	# -------------------------------------------------------------------
	def __len__(self):
		if self.count	==	-1:
			self.count	=	self.db.Count(
				self.objclass, 
				self.filter,
			)
		
		return self.count
	
	# -------------------------------------------------------------------
	def __getitem__(self, k):
		try:
			i	=	k.start
			j	=	k.stop
			k	=	k.step
			
			l	=	len(self)
			
			if i == None:
				i = 0
			elif i < 0:
				i = i + l
				
			if j == None:
				j = sys.maxint
			elif j < 0:
				j = j + l
			
			if k == None:
				k = 1
			
			ls	=	[]
			
			for x in xrange(i, j, k):
				if x >= l:
					break
				
				ls.append( self[x] )
			
			return ls
		except:
			pass		
		
		if k < 0 or k > len(self) - 1:
			raise IndexError('ListAdapter of %s does not have %d elements' 
				% (self.objclass, k)
			)
		
		if k >= self.cachestart and k < self.cachestart + len(self.cache):
			return self.cache[k - self.cachestart]
		
		if self.sort:
			limit	=	'ORDER BY %s LIMIT %s OFFSET %s' % (
				','.join(self.sort), 
				self.cachelimit,
				k,
			)
		else:
			limit	=	'LIMIT %s OFFSET %s' % (self.cachelimit, k)
		
		self.cache	=	self.db.Find(
			self.objclass, 
			'%s %s' % (self.filter, limit),
		)
		self.cachestart	=	k
		
		return self.cache[0]

	# -------------------------------------------------------------------
	def __delitem__(self, k):
		if k < 0 or k > len(self) - 1:
			raise IndexError('ListAdapter of %s does not have %d elements' 
				% (self.objclass, k)
			)
		
		# Make sure that k is in cache
		self[k]
			
		self.db.Delete( self.cache[k - self.cachestart] )
		self.cache	=	[]
		self.count	=	-1
		
